﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows_AI_Assistant.Models;

namespace Windows_AI_Assistant.Services
{
    public class DialogContentService
    {
        private const string dbPath = "Data Source=DialogContentData.db;Version=3;";
        public DialogContentService()
        {
            InitializeDatabase();
        }
        private void InitializeDatabase()
        {
            using (SQLiteConnection conn = new SQLiteConnection(dbPath))
            {
                conn.Open();
                string createTableQuery = "CREATE TABLE IF NOT EXISTS DialogContents (Id INTEGER PRIMARY KEY, DialogId TEXT, UserId TEXT, UserName TEXT, Role TEXT, Content TEXT, Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)";
                using (SQLiteCommand command = new SQLiteCommand(createTableQuery, conn))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public void AddDialogContent(DialogContent DialogContent)
        {
            using (SQLiteConnection conn = new SQLiteConnection(dbPath))
            {
                conn.Open();
                string insertQuery = "INSERT INTO DialogContents (DialogId, UserId, UserName, Role, Content, Timestamp) VALUES (@DialogId, @UserId, @UserName, @Role, @Content, @Timestamp)";
                using (SQLiteCommand command = new SQLiteCommand(insertQuery, conn))
                {
                    command.Parameters.AddWithValue("@DialogId", DialogContent.DialogId);
                    command.Parameters.AddWithValue("@UserId", DialogContent.UserId);
                    command.Parameters.AddWithValue("@UserName", DialogContent.UserName);
                    command.Parameters.AddWithValue("@Role", DialogContent.Role);
                    command.Parameters.AddWithValue("@Content", DialogContent.Content);
                    command.Parameters.AddWithValue("@Timestamp", DialogContent.Timestamp);
                    command.ExecuteNonQuery();
                }
            }
        }

        public List<DialogContent> GetAllDialogContents(DateTime startDate, DateTime endDate)
        {
            string startDateString = startDate.ToString("yyyy-MM-dd 00:00:00");
            string endDateString = endDate.ToString("yyyy-MM-dd 23:59:59");
            List<DialogContent> DialogContents = new List<DialogContent>();
            using (SQLiteConnection conn = new SQLiteConnection(dbPath))
            {
                conn.Open();
                string selectQuery = $"SELECT * FROM DialogContents WHERE Timestamp BETWEEN datetime('{startDateString}') AND datetime('{endDateString}')";
                using (SQLiteCommand command = new SQLiteCommand(selectQuery, conn))
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DialogContents.Add(new DialogContent
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                DialogId = reader["DialogId"].ToString(),
                                UserId = reader["UserId"].ToString(),
                                UserName = reader["UserName"].ToString(),
                                Role = reader["Role"].ToString(),
                                Content = reader["Content"].ToString(),
                                Timestamp = Convert.ToDateTime(reader["Timestamp"])
                            });
                        }
                    }
                }
            }
            return DialogContents;
        }
    }
}
